package com.wanmait.hotelManage.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.wanmait.hotelManage.util.DBHelper;
import com.wanmait.hotelManage.vo.Pager;
import com.wanmait.hotelManage.vo.RoomTypeComment;
import com.wanmait.hotelManage.vo.RoomTypeCommentReport;
import com.wanmait.hotelManage.vo.Userinfo;

public class RoomTypeCommentReportDAO {
	private RoomTypeCommentReportDAO() {};
	private static RoomTypeCommentReportDAO roomtypecommentreportdao=new RoomTypeCommentReportDAO();
	public static RoomTypeCommentReportDAO getRoomtypecommentreportdao() {
		return roomtypecommentreportdao;
	}
	 /**
	 * @param pager
	 * @return
	 */
	public List<RoomTypeCommentReport> findall(Pager pager){
		 List<RoomTypeCommentReport>RoomTypeCommentReports=new ArrayList<RoomTypeCommentReport>();
		 Connection conn=null;
		 PreparedStatement ps=null;
		 ResultSet rs=null;
		 StringBuilder sb=new StringBuilder();
		 sb.append(" SELECT room_type_comment_report.id,room_type_comment_report.reason,room_type_comment_report.userinfo_id,room_type_comment_report.room_type_comment_id,room_type_comment_report.input_time, ");
		 sb.append(" userinfo.username ");
		 sb.append(" FROM room_type_comment_report,room_type_comment,userinfo ");
		 sb.append(" WHERE room_type_comment.id=room_type_comment_report.room_type_comment_id AND room_type_comment_report.status=0 AND room_type_comment_report.userinfo_id=userinfo.id AND room_type_comment.enable=1  ORDER by room_type_comment_report.input_time desc limit ?,? ");
		 conn=DBHelper.getDBHelper().getConn();
		 try {
			ps=conn.prepareStatement(sb.toString());
			 ps.setInt(1, pager.getStart());
			 ps.setInt(2, pager.getPageSize());
			 rs=ps.executeQuery();
			 RoomTypeCommentReport rtcr=null;
			 Userinfo ui=null;
			 while(rs.next()) {
				 rtcr=new RoomTypeCommentReport();
				 rtcr.setId(rs.getInt("room_type_comment_report.id"));
				 rtcr.setInputTime(rs.getTimestamp("room_type_comment_report.input_time"));
				 rtcr.setReason(rs.getString("room_type_comment_report.reason"));
				 ui=new Userinfo();
				 ui.setId(rs.getInt("room_type_comment_report.userinfo_id"));
				 ui.setUsername(rs.getString("userinfo.username"));
				 rtcr.setUserinfo(ui);
				 RoomTypeCommentReports.add(rtcr);
			 }
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		 return RoomTypeCommentReports;
	 }
//	public static void main(String[] args) {
//		List<RoomTypeCommentReport>f=RoomTypeCommentReportDAO.getRoomtypecommentreportdao().findall();
//	for (RoomTypeCommentReport roomTypeCommentReport : f) {
//		System.out.println(roomTypeCommentReport.getUserinfo().getId());
//	}
//	}
	/**
	 * 通过
	 * @param i
	 */
	public void updateby(int i) {
		String sql="update room_type_comment_report set status=1 where id=?";
		DBHelper.getDBHelper().executeUpdate(sql, i);
	}
	/**
	 * 打回
	 * @param parseInt
	 */
	public void updateNoby(int parseInt) {
		String sql="update room_type_comment_report set status=1 where id=?";
		DBHelper.getDBHelper().executeUpdate(sql, parseInt);
	}
	/**
	 * 批量通过
	 * @param ids
	 */
	public void updatebys(String ids) {
		String sql="update room_type_comment_report set status=1 where id in("+ids+")";
		DBHelper.getDBHelper().executeUpdate(sql);
	}
	/**
	 * 批量打回
	 * @param ids
	 */
	public void updateNObys(String ids) {
		String sql="update room_type_comment_report set status=1 where id in("+ids+")";
		DBHelper.getDBHelper().executeUpdate(sql);
	}
	/**
	 * 评论举报的分页
	 * @return
	 */
	public int getrows(){
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		StringBuilder sb=new StringBuilder();
		sb.append(" select count(*) c from room_type_comment_report,room_type_comment,userinfo where room_type_comment.id=room_type_comment_report.room_type_comment_id ");
		sb.append(" AND room_type_comment_report.status=0 AND room_type_comment_report.userinfo_id=userinfo.id and room_type_comment.enable=1");
		conn=DBHelper.getDBHelper().getConn();
		try {
			ps=conn.prepareStatement(sb.toString());
			rs=ps.executeQuery();
			rs.next();
			Integer count=rs.getInt("c");
			return count;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return 0;
	}
//	public static void main(String[] args) {
//		String ids="1,2,3";
//		List<Integer>a=RoomTypeCommentReportDAO.getRoomtypecommentreportdao().findRoomTypeCommentId(ids);
//		for (Integer integer : a) {
//			System.out.println(a);	
//		}
//	}
	/**
	 * 获取RoomTypeCommentId的值
	 * @param ids
	 * @return
	 */
	public List<Integer> findRoomTypeCommentId(String ids) {
		List<Integer>i=new ArrayList<Integer>();
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql="select room_type_comment_id from room_type_comment_report where id in("+ids+")";
		conn=DBHelper.getDBHelper().getConn();
		try {
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()) {
				i.add(rs.getInt("room_type_comment_id"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return i;
	}
	/**
	 * 添加举报
	 * @param id
	 * @param id1
	 * @param a 
	 */
	public void insert(Integer id, Integer id1, String a) {
		String sql="insert into room_type_comment_report(userinfo_id,room_type_comment_id,reason) values(?,?,?)";
		DBHelper.getDBHelper().executeUpdate(sql, id1,id,a);
	}
	/**
	 * 搜索做的模糊查询
	 * @param text
	 * @param pager
	 * @return
	 */
	public List<RoomTypeCommentReport> findname(String text,Pager pager) {
		List<RoomTypeCommentReport>rtcrs=new ArrayList<RoomTypeCommentReport>();
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		StringBuilder sb=new StringBuilder();
		 sb.append(" SELECT room_type_comment_report.id,room_type_comment_report.reason,room_type_comment_report.userinfo_id,room_type_comment_report.room_type_comment_id,room_type_comment_report.input_time, ");
		 sb.append(" userinfo.username ");
		 sb.append(" FROM room_type_comment_report,room_type_comment,userinfo ");
		 sb.append(" WHERE room_type_comment.id=room_type_comment_report.room_type_comment_id AND room_type_comment_report.status=0 AND room_type_comment_report.userinfo_id=userinfo.id AND room_type_comment.enable=1 and userinfo.username like '%"+text+"%' ORDER by room_type_comment_report.input_time desc LIMIT ?,? ");
		 conn=DBHelper.getDBHelper().getConn();
		 try {
			ps=conn.prepareStatement(sb.toString());
			 ps.setInt(1, pager.getStart());
			 ps.setInt(2, pager.getPageSize());
			 rs=ps.executeQuery();
			 RoomTypeCommentReport rtcr=null;
			 Userinfo ui=null;
			 while(rs.next()) {
				 rtcr=new RoomTypeCommentReport();
				 rtcr.setId(rs.getInt("room_type_comment_report.id"));
				 rtcr.setInputTime(rs.getTimestamp("room_type_comment_report.input_time"));
				 rtcr.setReason(rs.getString("room_type_comment_report.reason"));
				 ui=new Userinfo();
				 ui.setId(rs.getInt("room_type_comment_report.userinfo_id"));
				 ui.setUsername(rs.getString("userinfo.username"));
				 rtcr.setUserinfo(ui);
				 rtcrs.add(rtcr);
			 }
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return rtcrs;
	}
	/**
	 * 搜索的行数
	 * @param text
	 * @return
	 */
	public int getrowss(String text) {
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		StringBuilder sb=new StringBuilder();
		sb.append(" select count(*) c from room_type_comment_report,room_type_comment,userinfo where room_type_comment.id=room_type_comment_report.room_type_comment_id ");
		sb.append(" AND room_type_comment_report.status=0 AND room_type_comment_report.userinfo_id=userinfo.id and userinfo.username like '%"+text+"%' and room_type_comment.enable=1");
		conn=DBHelper.getDBHelper().getConn();
		try {
			ps=conn.prepareStatement(sb.toString());
			rs=ps.executeQuery();
			rs.next();
			Integer count=rs.getInt("c");
			return count;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return 0;
	}
//	public static void main(String[] args) {
//		String text="a";
//		int rows=RoomTypeCommentReportDAO.getRoomtypecommentreportdao().getrowss(text);
//		System.out.println(rows);
//	}
	public void updateoks(String ids) {
		String sql="update room_type_comment_report set ok=1 where id in("+ids+")";
		DBHelper.getDBHelper().executeUpdate(sql);
	}
	public void updateok(Integer id) {
		String sql="update room_type_comment_report set ok=1 where id =?";
		DBHelper.getDBHelper().executeUpdate(sql,id);
	}
}
